﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace WindowsFormsApp4
{
    class DbHelperByParameter
    {
        /// <summary>
        /// 连接字符串
        /// </summary>
        public static string ConnStr = DbHelperBySql.conString;

        /// <summary>
        ///  返回数据集
        /// </summary>
        /// <param name="sqlcmd"></param>
        /// <returns></returns>
        public DataSet ExecuteTable(String Sqlstr)
        {
            SqlConnection con = new SqlConnection(ConnStr);
            try
            {
                //创建DataAdapter数据适配器实例 读取数据
                using (SqlDataAdapter adapter = new SqlDataAdapter(Sqlstr, con))
                {
                    con.Open();
                    DataSet ds = new DataSet();
                    adapter.Fill(ds);
                    //返回一个数据集
                    return ds;
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            finally
            {
                con.Close();
            }
        }

        /// <summary>
        /// 返回数据集根据传输的 表名 列名 条件
        /// </summary>
        /// /// <param name="FindType">查找类型 1正常 ，2.模糊查询</param>
        /// <param name="viewName">表名</param>
        /// <param name="fields">列</param>
        /// <param name="strWhere">条件</param>
        /// <returns></returns>
        public DataSet GetView(int FindType, string[] fields, string viewName, Dictionary<string, string> strWhere)
        {
            //定义条件
            string Where = "";
            StringBuilder strSql = new StringBuilder();

            string field = string.Join(",", fields);


            if (FindType == 1)
            {

                foreach (var item in strWhere)
                {
                    Where += string.Format("{0}='{1}' and ", item.Key.ToString(), item.Value.ToString());

                }
                Where = Where.Substring(0, Where.Length - 4);
            }
            if (FindType == 2)
            {

                foreach (var item in strWhere)
                {
                    Where += string.Format("{0} like '%{1}%' or ", item.Key.ToString(), item.Value.ToString());
                }
                Where = Where.Substring(0, Where.Length - 3);
            }

            strSql.Append("select  " + field + " from " + viewName.Trim() + " where " + Where);
            return ExecuteTable(strSql.ToString());
        }

        /// <summary>
        ///  用过sql语句查询到当前数据第一行第一列的值
        /// </summary>
        /// <param name="Sqlstr"></param>
        /// <returns></returns>
        public object GetDataCount(string Sqlstr)
        {
            SqlConnection con = new SqlConnection(ConnStr);

            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = Sqlstr;
                    cmd.Connection = con;

                    con.Open();
                    //返回第一行第一列的值
                    return cmd.ExecuteScalar();
                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

        }

        /// <summary> 
        /// AddOrDeleteOrUpdate 针对insert,update,delete
        /// 对连接执行 Transact-SQL 语句并返回受影响的行数
        /// </summary> 
        /// <param name="Sqlstr">SQL语句</param> 
        /// <param name="param">参数对象数组</param> 
        /// <returns></returns> 
        public int AddOrDeleteOrUpdate(String Sqlstr, SqlParameter[] param)
        {
            //连接数据库
            SqlConnection conn = new SqlConnection(ConnStr);
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.Connection = conn;
                    cmd.CommandText = Sqlstr;

                    //将参数批量传入sql命令中
                    cmd.Parameters.AddRange(param);
                    conn.Open();
                    return cmd.ExecuteNonQuery();

                }
            }
            catch (Exception e)
            {
                throw new Exception(e.Message.ToString());
            }
            finally
            {
                conn.Close();
                conn.Dispose();
            }

        }

        /// <summary>
        /// 用于登录
        /// </summary>
        /// <returns></returns>
        public object GetLoginCouent(string username, string pwd)
        {
            StringBuilder sqlstr = new StringBuilder("select count(*) from Users where UserName=@username and PassWord=@password");
            SqlConnection con = new SqlConnection(ConnStr);
            try
            {
                using (SqlCommand cmd = new SqlCommand())
                {
                    cmd.CommandText = sqlstr.ToString();
                    cmd.Connection = con;

                    //添加单个参数时
                    //cmd.Parameters.AddWithValue("@username",username);

                    //添加多个参数
                    //    SqlParameter[] para =
                    //        {
                    //     new SqlParameter("@username",SqlDbType.VarChar,20),
                    //     new SqlParameter("@password",SqlDbType.VarChar,20),
                    //};

                    //    para[0].Value = username;
                    //    para[1].Value = pwd;


                    SqlParameter[] para =
                      {
                     new SqlParameter("@username",username),
                     new SqlParameter("@password",pwd),
                     };

                    cmd.Parameters.AddRange(para);
                    con.Open();
                    //返回第一行第一列的值
                    return cmd.ExecuteScalar();



                }
            }
            catch (Exception e)
            {

                throw new Exception(e.Message);
            }
            finally
            {
                con.Close();
                con.Dispose();

            }

        }

        //事务执行 
        public void ExecTransaction(string sqlStr)
        {
            SqlConnection con = new SqlConnection(ConnStr);

            using (SqlCommand cmd = new SqlCommand())
            {


                SqlTransaction tran;

                //开始事务
                tran = con.BeginTransaction();
                cmd.Connection = con; //指定事务的数据库连接
                cmd.Transaction = tran;  //指定事务
                cmd.CommandText = sqlStr;
                try
                {
                    con.Open();
                    cmd.ExecuteNonQuery();

                    tran.Commit();//提交事务


                }
                catch (Exception e)
                {

                    throw new Exception(e.Message);
                    tran.Rollback();  //回滚事务
                }
                finally
                {
                    con.Close();
                    con.Dispose();
                }

            }

        }

        /// <summary>
        /// 执行存储过程 
        /// </summary>
        /// <param name="userId"></param>
        /// <returns></returns>
        public object GetUsernameBy(string Username)
        {
            SqlConnection con = new SqlConnection(ConnStr);
            try
            {
                //using (SqlCommand cmd = new SqlCommand("GetUserName", con))
                //{

                //    //案例--根据用户id获取用户名

                //    SqlParameter[] para =
                //         {
                //       new SqlParameter("@UserId",userId),
                //       new SqlParameter("@Username",SqlDbType.NVarChar,50)
                //       {
                //          Direction=ParameterDirection.Output         //输出参数
                //       }
                //   };


                //    cmd.Parameters.AddRange(para);
                //    cmd.CommandType = CommandType.StoredProcedure;   //定义命令为存储过程
                //    con.Open();
                //    cmd.ExecuteScalar();      //执行语句
                //    return para[1].Value;
                //}

                //输入输出参数 的使用
                using (SqlCommand cmd = new SqlCommand("GetUserNameNew", con))
                {
                    SqlParameter[] para =
                   {
                        new SqlParameter("@Username",SqlDbType.NVarChar,50)
                        {
                           Direction=ParameterDirection.InputOutput         //输入输出参数
                        }
                    };

                    para[0].Value = Username;
                    cmd.Parameters.AddRange(para);
                    cmd.CommandType = CommandType.StoredProcedure;   //定义命令为存储过程
                    con.Open();
                    cmd.ExecuteScalar();      //执行语句
                    return para[0].Value;
                }

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

        }


        /// <summary>
        ///  返回值参数 的使用
        /// </summary>
        /// <param name="UserId"></param>
        /// <param name="Username"></param>
        /// <returns></returns>
        public object GetUsernameByPwd(int UserId,string Username)
        {
            SqlConnection con = new SqlConnection(ConnStr);
            try
            {
           
                using (SqlCommand cmd = new SqlCommand("GetUserpassword", con))
                {
                    cmd.CommandType = CommandType.StoredProcedure;   //定义命令为存储过程

                    SqlParameter refValue = new SqlParameter("@refValue", SqlDbType.NVarChar, 50)
                    {
                        Direction = ParameterDirection.ReturnValue    //定义当前参数为返回值参数
                    };

                    SqlParameter[] para =
                   {
                        new SqlParameter("@UserId",UserId),
                        new SqlParameter("@Username",Username),
                        refValue

                    };

                    cmd.Parameters.AddRange(para);           
                    con.Open();
                    cmd.ExecuteScalar();      //执行语句
                    return para[2].Value;

                }

            }
            catch (Exception e)
            {
                throw new Exception(e.Message);
            }
            finally
            {
                con.Close();
                con.Dispose();
            }

        }

    }
}
